System Statistics – “Exadata” Mode
Here’s a very quick note on a relatively recent addition to System Statistics. It’s the so called “Exadata mode”. This mode is intended to give the optimizer a little more info about the storage system on the Exadata platform. Here’s some info from an MOS note.
——————————————————————————————
Oracle Sun Database Machine Setup/Configuration Best Practices [ID 1274318.1]
Verify Exadata specific optimizer statistics have been gathered
Benefit / Impact
Gathering Exadata specific system statistics ensure the optimizer is aware of Exadata scan speed. Accurately accounting for the speed of scan operations will ensure the Optimizer chooses an optimal execution plan in a Exadata environment. The following command gathers Exadata specific system statistics
exec dbms_stats.gather_system_stats(‘EXADATA’);
Risk:
Lack of Exadata specific stats can lead to less performant optimizer plans.
Action / Repair:
To see if Exadata specific optimizer stats have been gathered, run the following query on a system with at least 11.2.0.2 BP18 or 11.2.0.2 BP8 Oracle software. If PVAL1 returns null or is not set, Exadata specific stats have not been gathered.
select pname, PVAL1 from aux_stats$ where pname='MBRC';
——————————————————————————————-
And here’s an example of gathering Exadata mode system stats on a real Exadata as performed by a real Enkitec employee.
SYS@dbm1> exec dbms_stats.delete_system_stats;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SYS@dbm1> @system_stats
PNAME PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS 0
CPUSPEEDNW 2797
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
13 rows selected.
Elapsed: 00:00:00.00
SYS@dbm1> exec dbms_stats.gather_system_stats('EXADATA');
PL/SQL procedure successfully completed.
Elapsed: 00:01:45.51
SYS@dbm1> select pname, pval1 from sys.aux_stats$;
PNAME PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS 1
CPUSPEEDNW 2832
IOSEEKTIM 9
IOTFRSPEED 88419
SREADTIM
MREADTIM
CPUSPEED
MBRC 128
MAXTHR
SLAVETHR
13 rows selected.
Elapsed: 00:00:00.00
So just a couple of quick notes. First, gathering “Exadata” mode system stats does spend some time gathering NOWORKLOAD stats (1:45 in this case) but appears to manually set MBRC to 128.
Note that this is not hardcoded, but actually uses the value of the db parameter db_file_multiblock_read_count which in our case defaults to 128. (see comment from Chris Antognini below) Increasing MBRC makes sense because it would tend to push the optimizer towards full table scans which can obviously be a lot faster on Exadata due to Smart Scan offloading. The default for costing is 8 and pushing it to 128 does make full scans more attractive to the optimizer. Note also that the IOTFRSPEED was dramatically increased (although this doesn’t always happen).
Here’s the section from a 10053 (Wolfgang) trace just for verification.
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2832 millions instructions/sec (default is 100)
IOTFRSPEED: 88419 bytes per millisecond (default is 4096)
IOSEEKTIM: 9 milliseconds (default is 10)
MBRC: 128 blocks (default is 8)
I do think that Exadata mode system stats should be set (collected) on new Exadata implementations.
Hi Kerry
I observed that the value is not necessarily set to 128. Instead, it is set to db_file_multiblock_read_count. So, if you don’t set db_file_multiblock_read_count, it’s generally (but, not always) set to 128. If the parameter is set to a lower or higher value, mbrc will be set accordingly.
Could you please try to reproduce that behavior?
Thank you,
Chris
Hi Chris,
You are correct. It does set it to whatever db_file_multiblock_read_count is set to. So on systems that have not changed the setting, it will set MBRC to 128 (at least on the current 11.2.0.3 version on Exadata X2,X3). As you are aware, db_file_multiblock_read_count controls two underscore parameters (_db_file_optimizer_read_count for costing – default 8 and _db_file_exec_read_count for execution – default 128), so when left to the default, these two split the costing from execution making multi-block reads large without encouraging the optimizer to use full scans. So I was interested if the calculation used one of these under the covers. But apparently it uses the actual setting of db_file_multiblock_read_count. Anyway, here’s an example:
SYS@dbm1> @system_stats PNAME PVAL1 ------------------------------ ---------- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 2797 IOSEEKTIM 9 IOTFRSPEED 4096 SREADTIM MREADTIM CPUSPEED MBRC 128 MAXTHR SLAVETHR 13 rows selected. SYS@dbm1> @parms Enter value for parameter: db_file_multiblock_read_count Enter value for isset: Enter value for show_hidden: NAME VALUE ISDEFAUL ISMODIFIED ISSET -------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ---------- db_file_multiblock_read_count 128 TRUE FALSE FALSE SYS@dbm1> alter system set db_file_multiblock_read_count=256; System altered. SYS@dbm1> @parms Enter value for parameter: db_file_multiblock_read_count Enter value for isset: Enter value for show_hidden: NAME VALUE ISDEFAUL ISMODIFIED ISSET -------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ---------- db_file_multiblock_read_count 256 TRUE TRUE TRUE SYS@dbm1> exec dbms_stats.gather_system_stats('EXADATA'); PL/SQL procedure successfully completed. SYS@dbm1> @system_stats; PNAME PVAL1 ------------------------------ ---------- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 2795 IOSEEKTIM 9 IOTFRSPEED 85346 SREADTIM MREADTIM CPUSPEED MBRC 256 MAXTHR SLAVETHR 13 rows selected.Hi Kerry,
It will set it to MBRC to 128 depending on the db_cache_size, one of my customers is running database with a small sga on there X2/X3’s and the MBRC did not go to 128 we did some tests with ASMM disabled:
SQL> show parameter db_cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_advice string ON db_cache_size big integer 560M SQL> exec dbms_stats.delete_system_stats(); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_system_stats('EXADATA'); PL/SQL procedure successfully completed. SQL> select pname, pval1 from aux_stats$ where pname='MBRC'; PNAME PVAL1 ------------------------------ ---------- MBRC 42 SQL> alter system set db_cache_size=2G scope=spfile; System altered. SQL> exit Bounce the database SQL> exec dbms_stats.delete_system_stats(); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_system_stats('EXADATA'); PL/SQL procedure successfully completed. SQL> select pname, pval1 from aux_stats$ where pname='MBRC'; PNAME PVAL1 ------------------------------ ---------- MBRC 128Hi Klaas-jan,
Thanks for the info. I’m surprised by that actually. As Tanel is fond of saying, “the rabbit hole always goes deeper”. I don’t have time for further testing at the moment but hope to get back to it soon. I’m also wondering why the IOTFRSPEED seems to sometimes get set and other times reverts back to the default 4096.
Kerry
Thanks for the post. I see that Oracle has published bug “Bug 17501565 – DBMS_STATS.gather_system_stats on Exadata gets inconsistent measurement of IOTFRSPEED (Doc ID 17501565.8)” related to your observations of IOTFRSPEED being set to 4096.
[…] aware and thus let the optimizer make choices that might result in offloading more often. See this blog post from Kerry Osborne about it, i made a small comment about the MBRC in it and i would like […]
[…] mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only think I would like to add is that the […]
Have you tried setting MBRC to a value larger than 128?
128 used to be the max value for MBRC, and still is for buffered reads (given an 8kb blocksize), but the maximal used value observed when doing direct path reads with MBRC set to 1024 or higher is 1024 (tested on 11.2.0.3).
Hi Frits,
Yes – you can definitely set it higher than 128 now as discussed in a previous comment.
Kerry
[…] very recently, the note with DocId 368252.1 ‘FAQ – Collecting Statistics in Oracle EBS 11i and R12’ in MOS (My Oracle Support) was […]
Hi Kerry
As ASM AU size is 4MB and also recommended to have extent size for optimal i/o in exadta.
Shouldn’t MBRC with block size should be in align to get benefits for large i/o.
Thanks Krishan
Interesting thought. Give it a try and report back your findings. I’d love to see the results.
Kerry